﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TravelSite.Entities;
using Dapper;

namespace TravelSite.DAL.Repositories
{
    public class ApiTravelPackageRepository : BaseRepository<TravelPackage>
    {
        protected override string TableName
        {
            get { return "[TravelPackage]"; }
        }

        public override IEnumerable<TravelPackage> FindAll()
        {
            string strSQL = @"  select tp.PackageId, tp.Cost, tp.PackageName, tp.VacationLength,
                    convert(bit, case 
                                    when exists(select 1 from PackageDestination (nolock) as pd where tp.PackageId = pd.PackageId) 
                                        or exists(select 1 from ClientPackage (nolock) as cp where tp.PackageId = cp.PackageId)  then 0
                                else 1 end) as CanDelete
                    from [TravelPackage] (nolock) as tp ";

            IEnumerable<TravelPackage> result = Connection.Query<TravelPackage>(strSQL);
            /*foreach (TravelPackage item in result)
                item.Destinations = FindDestinations(item.PackageId);*/

            return result;
        }

        public override TravelPackage Find(int id)
        {
            TravelPackage result = Connection.Query<TravelPackage>(
                @"  select tp.PackageId, tp.Cost, tp.PackageName, tp.VacationLength
                    from [TravelPackage] (nolock) as tp
                    WHERE tp.PackageId = @PackageId", new { PackageId = id }).SingleOrDefault();
            if (result != null)
                result.Destinations = FindDestinations(result.PackageId);

            return result;
        }

        public TravelPackage FindClientPackage(int travelId)
        {
            TravelPackage result = Connection.Query<TravelPackage>(
                @"  select tp.PackageId, tp.Cost, tp.PackageName, tp.VacationLength,
                        cp.PkgDestId, cp.ClientId, cp.Status, cp.StartDate, cp.ExpDate
                    from [TravelPackage] (nolock) as tp inner join ClientPackage (nolock) as cp on tp.PackageId = cp.PackageId
                    WHERE cp.TravelId = @TravelId", new { TravelId = travelId }).SingleOrDefault();
            
            return result;
        }

        public override void Insert(TravelPackage item)
        {
            Connection.Execute(
                @"insert into [TravelPackage] 
                (Cost, PackageName, VacationLength)
                values (@Cost, @PackageName, @VacationLength)", item);
        }

        public override void Update(TravelPackage item)
        {
            Connection.Execute(
                @"update TravelPackage
                    set Cost = @Cost, PackageName = @PackageName, VacationLength = @VacationLength
                    where (PackageId = @PackageId)", item);
        }

        public override void Delete(int id)
        {
            Connection.Execute(
                @"delete 
                    from TravelPackage
                    where (PackageId = @PackageId)", new { PackageId = id });
        }

        public IEnumerable<TravelPackage> FindByClientNoDestination(int contactId)
        {
            string strSQL = @"select tp.PackageId, tp.Cost, tp.PackageName, tp.VacationLength, cp.TravelId
                    from [TravelPackage] (nolock) as tp inner join ClientPackage (nolock) as cp on tp.PackageId = cp.PackageId
                    where (cp.ClientId = @ClientId)
                        and (cp.PkgDestId is null)";

            IEnumerable<TravelPackage> result = Connection.Query<TravelPackage>(strSQL, new { ClientId = contactId });
            foreach (TravelPackage item in result)
                item.Destinations = FindDestinations(item.PackageId);

            return result;
        }

        public List<TravelPackage> FindByClient(int contactId)
        {
            string strSQL = @"select tp.PackageId, tp.Cost, tp.PackageName, tp.VacationLength, cp.TravelId, PkgDestId = isnull(cp.PkgDestId, -1),
                            cp.Status, cp.StartDate, cp.ExpDate
                    from [TravelPackage] (nolock) as tp inner join ClientPackage (nolock) as cp on tp.PackageId = cp.PackageId
                    where (cp.ClientId = @ClientId)";

            IEnumerable<TravelPackage> result = Connection.Query<TravelPackage>(strSQL, new { ClientId = contactId }).Where(i=>i.ComputedStatus != "EXPIRED");
            foreach (TravelPackage item in result)
                item.Destinations = FindDestinations(item.PackageId);

            return result.ToList();
        }
        
        public IEnumerable<PackageDestination> FindDestinations(int id)
        {
            return Connection.Query<PackageDestination>(@"select pd.PkgDestId, pd.PackageId, pd.DestinationName, isnull(vw.OptionCount, 0) as OptionCount,
                                convert(bit, case 
                                    when exists(select 1 from PackageDestinationOption (nolock) as pdo where pd.PkgDestId = pdo.PkgDestId) 
                                        or exists(select 1 from ClientPackage (nolock) as cp where pd.PkgDestId = cp.PkgDestId)  then 0
                                else 1 end) as CanDelete
                    from [PackageDestination] (nolock) as pd left join (select PkgDestId, count(*) as OptionCount from PackageDestinationOption (nolock) group by PkgDestId) as vw on pd.PkgDestId = vw.PkgDestId
                    where (pd.PackageId = @PackageId)", new { PackageId = id });
        }

        public PackageDestination FindDestination(int id)
        {
            return Connection.Query<PackageDestination>(@"select pd.PkgDestId, pd.PackageId, pd.DestinationName, isnull(vw.OptionCount, 0) as OptionCount
                    from [PackageDestination] (nolock) as pd left join (select PkgDestId, count(*) as OptionCount from PackageDestinationOption (nolock) group by PkgDestId) as vw on pd.PkgDestId = vw.PkgDestId
                    where (pd.PkgDestId = @PkgDestId)", new { PkgDestId = id }).SingleOrDefault();
        }

        public void InsertDestination(PackageDestination item)
        {
            Connection.Execute(
                @"insert into [PackageDestination] 
                (PackageId, DestinationName)
                values (@PackageId, @DestinationName)", item);
        }

        public void UpdateDestination(PackageDestination item)
        {
            Connection.Execute(
                @"update PackageDestination
                    set DestinationName = @DestinationName
                    where (PkgDestId = @PkgDestId)", item);
        }

        public void DeleteDestination(int id)
        {
            Connection.Execute(
                @"delete 
                    from PackageDestination
                    where (PkgDestId = @PkgDestId)", new { PkgDestId = id });
        }

        public bool CheckTravel(int travelId, int contactId)
        {
            return Connection.Query<bool>(@"select convert(bit, case when exists(select 1 from ClientPackage (nolock) where TravelId = @TravelId and ClientId = @ClientId) then 1 else 0 end)",
                new { TravelId = travelId, ClientId = contactId }).Single();
        }

        /*public int SetPkgDest(int travelId, int contactId, int pkgDestId)
        {
            return Connection.Query<int>("spAPI_ClientPackage_SetDestination @TravelId = @TravelId, @ClientId = @ClientId, @PkgDestId = @PkgDestId",
                new { TravelId = travelId, ClientId = contactId, PkgDestId = pkgDestId }).Single();
        }*/

        public int SetPkgChoice(int travelId, int contactId, int pkgDestId, string choiceIdList, DateTime? startDate)
        {
            return Connection.Query<int>("spAPI_ClientPackageChoice_Set @TravelId = @TravelId, @ClientId = @ClientId, @PkgDestId = @PkgDestId, @ChoiceId = @ChoiceId, @StartDate = @StartDate",
                new { TravelId = travelId, ClientId = contactId, PkgDestId = pkgDestId, ChoiceId = choiceIdList, StartDate = startDate }).Single();
        }

        public IEnumerable<PackageDestinationOption> FindOptions(int id)
        {
            IEnumerable<PackageDestinationOption> result = Connection.Query<PackageDestinationOption>(
                    @"select pdo.PkgDestId, pdo.OptionId, vo.OptionName,
                        convert(bit, case 
                                    when exists(select 1 from OptionChoice (nolock) as och where och.PkgDestId = pdo.PkgDestId and och.OptionId = pdo.OptionId) then 0
                                else 1 end) as CanDelete
                    from [PackageDestinationOption] (nolock) as pdo left join VacationOption (nolock) as vo on pdo.OptionId = vo.OptionId 
                    where (pdo.PkgDestId = @PkgDestId)", new { PkgDestId = id });
            
            return result;
        }

        public IEnumerable<PackageDestinationOption> FindOptions(int id, int travelId)
        {
            IEnumerable<PackageDestinationOption> result = Connection.Query<PackageDestinationOption>(
                    @"select pdo.PkgDestId, pdo.OptionId, vo.OptionName,
                            isnull((select och.ChoiceId
                            from OptionChoice (nolock) as och inner join ClientPackageChoice (nolock) as cpch on och.ChoiceId = cpch.ChoiceId
                            where och.OptionId = pdo.OptionId
                                and cpch.TravelId = @TravelId), -1) as SelectedChoiceId
                    from [PackageDestinationOption] (nolock) as pdo left join VacationOption (nolock) as vo on pdo.OptionId = vo.OptionId 
                    where (pdo.PkgDestId = @PkgDestId)", new { PkgDestId = id, TravelId = travelId });

            foreach (PackageDestinationOption item in result)
                item.Choices = FindOptionsChoice(item.PkgDestId, item.OptionId, travelId);
            

            return result;
        }

        public void InsertOption(PackageDestinationOption item)
        {
            Connection.Execute(
                @"insert into [PackageDestinationOption] 
                (PkgDestId, OptionId)
                values (@PkgDestId, @OptionId)", item);
        }

        public void DeleteOption(int pkgDestId, int optionId)
        {
            Connection.Execute(
                @"delete 
                    from PackageDestinationOption
                    where (PkgDestId = @PkgDestId) 
                        and (OptionId = @OptionId)", new { PkgDestId = pkgDestId, OptionId = optionId });
        }

        public IEnumerable<OptionChoice> FindOptionsChoice(int pkgDestId, int optionId, int travelId)
        {
            return Connection.Query<OptionChoice>(@"select oc.ChoiceId, oc.PkgDestId, oc.OptionId, oc.ChoiceDescription, cpch.OverrideChoice
                    from [OptionChoice] (nolock) as oc left join ClientPackageChoice (nolock) as cpch on oc.ChoiceId = cpch.ChoiceId and cpch.TravelId = @TravelId
                    where (oc.PkgDestId = @PkgDestId) 
                        and (oc.OptionId = @OptionId)", new { PkgDestId = pkgDestId, OptionId = optionId, TravelId = travelId });
        }

        public string PkgDestName(int pkgDestId)
        {
            return Connection.Query<string>(@"select tp.PackageName + ' - ' + pd.DestinationName
                    from [PackageDestination] (nolock) as pd left join TravelPackage (nolock) as tp on pd.PackageId = tp.PackageId
                    where (pd.PkgDestId = @PkgDestId)", new { PkgDestId = pkgDestId }).SingleOrDefault();
        }

    }
}
